package com.hkw.dao.impl;
import com.hkw.dao.IStudentDao;
import com.hkw.model.Student;
import com.hkw.utils.DruidUtils;
import com.hkw.utils.PagesUtils;
import com.hkw.utils.StudentHandler;
import com.mysql.jdbc.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *  IStudentDao的实现类，负责具体的方法实现
 */
public class StudentDaoImpl implements IStudentDao {
    /**
     * 查询所有学生信息
     * @param pagesUtils
     * @return
     * @throws SQLException
     */
    @Override
    public List<Student> findAll(PagesUtils pagesUtils) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        //判断当前所在页是否超过最大页数，如果超过，则将当前所在页变为最大页数
        if(pagesUtils.getNowPage()>pagesUtils.getPages()){
            pagesUtils.setNowPage(pagesUtils.getPages());
        }
        //判断当前所在页是否小于1，如果小，则将当前所在页变为1
        if(pagesUtils.getNowPage()<1){
            pagesUtils.setNowPage(1);
        }
        String sql = "select * from student limit ?,?";
        //使用匿名内部类自己手动将查询数据封装成一个list
        List<Student> list = qr.query(con, sql, new ResultSetHandler<List<Student>>() {
            List<Student> list = new ArrayList<>();
            @Override
            public List<Student> handle(ResultSet resultSet) throws SQLException {
                while(resultSet.next()) {
                    Student student = new Student();
                    student.setId(resultSet.getInt("id"));
                    student.setBirthday(resultSet.getDate("birth").toString());
                    student.setEmail(resultSet.getString("email"));
                    student.setName(resultSet.getString("name"));
                    student.setSex(resultSet.getString("sex"));
                    student.setRemark(resultSet.getString("remark"));
                    list.add(student);
                }
                return list;
            }
        },pagesUtils.getBeginrow(),pagesUtils.getMaxrows());
        con.close();
        return list;
    }

    /**
     * 查询学生个数
     * @return
     * @throws SQLException
     */
    @Override
    public int findAllRows() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        String oldsql = "select count(id) from student";
        Object ob = qr.query(con, oldsql, new ResultSetHandler<Object>() {
            @Override
            public Object handle(ResultSet resultSet) throws SQLException {
                Object ob = null;
                while (resultSet.next()) {
                    ob = resultSet.getInt(1);
                }
                return ob;
            }
        });
        int total = (Integer)ob;
        con.close();  //释放连接
        return total;
    }

    /**
     *  添加学生
     * @param student
     * @return
     * @throws SQLException
     */
    @Override
    public int insertStudent(Student student) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        String sql = "insert into student(name,sex,birth,email,remark) values(?,?,?,?,?)";
        int update = qr.update(con, sql, student.getName(),student.getSex(),student.getBirthday(),student.getEmail(),student.getRemark());
        con.close();
        return update;
    }

    /**
     * 删除学生根据id
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public int deleteStudentById(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        String sql = "delete from student where id = ?";
        int update = qr.update(con, sql,id);
        con.close();
        return update;
    }

    /**
     * 查找学生根据id
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public Student findStudentById(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        String sql = "select * from student where id = ?";
        Student student = qr.query(con, sql, new StudentHandler(), id);
        con.close();
        return student;
    }

    /**
     * 跟新学生信息
     * @param student
     * @return
     * @throws SQLException
     */
    @Override
    public int updateStudent(Student student) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        String sql = "update student set name=?,sex=?,birth=?,email=?,remark=? where id=?";
        int update = qr.update(con, sql, student.getName(), student.getSex(), student.getBirthday(), student.getEmail(), student.getRemark(), student.getId());
        con.close();
        return update;
    }

    /**
     * 根据name和email查找学生
     * @param name
     * @param email
     * @return
     * @throws SQLException
     */
    @Override
    public Student findStudentByNameAndEmail(String name, String email) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        StringBuffer sql = new StringBuffer();
        Student student = null;
        //根据name和email是否有值，来拼接一个sql语句，最后释放连接
        sql.append("select * from student ");
        if(!StringUtils.isNullOrEmpty(name) && !StringUtils.isNullOrEmpty(email) ){
            sql.append("where name = ? and email = ?");
            student = qr.query(con,sql.toString(),new StudentHandler(),name,email);
        }else if(!StringUtils.isNullOrEmpty(name)){
            sql.append("where name = ?");
            student = qr.query(con,sql.toString(),new StudentHandler(),name);
        }else if(!StringUtils.isNullOrEmpty(email)){
            sql.append("where email = ?");
            student = qr.query(con,sql.toString(),new StudentHandler(),email);
        }else{
            student = qr.query(con,sql.toString(),new StudentHandler());
        }
        con.close();
        return student;
    }
}
